﻿using CsvHelper;
using Delphi.Mimir.Config;
using Delphi.Mimir.Data.Entities.Final;
using System.Data.Entity;
using System.Data.Entity.Migrations;
using System.IO;
using System.Linq;
using System.Reflection;
using System.Text;

namespace Delphi.Mimir.Migrations {
    // DropCreateDatabaseIfModelChanges<DelphiContext> 
    // DropCreateDatabaseAlways<DelphiContext>
    public class Initializer : DropCreateDatabaseIfModelChanges<DelphiContext> {
        protected override void Seed(DelphiContext context) {
            base.Seed(context);

            /******************************************************************
            /* Load massive data directly from csv to database (bypassing ef)
            /*****************************************************************/
            // Load RawSymbols
            //IList<RawSymbol> s = CsvLoader<RawSymbol>.LoadCsv("Delphi.Mimir.Data.Seed.RawSymbols.csv", Encoding.UTF8);
            //CsvLoader<RawSymbol>.BulkLoad(s, "raw.eod_symbols");

            // Load RawQuotes
            //IList<RawQuote> q = CsvLoader<RawQuote>.LoadCsv("Delphi.Mimir.Data.Seed.RawQuotes.csv", Encoding.UTF8);
            //CsvLoader<RawQuote>.BulkLoad(q, "raw.eod_quotes");

            /******************************************************************
            /* Lesser data seeded from csv through ef
            /* Copied from: http://www.davepaquette.com/archive/2014/03/18/seeding-entity-framework-database-from-csv.aspx
            /*****************************************************************/

            Assembly assembly = Assembly.GetExecutingAssembly();
            context.Configuration.AutoDetectChangesEnabled = false;
            string resourceNameBaseData = "Delphi.Mimir.Data.Seed.";
            string resourceNameBaseSql = "Delphi.Mimir.Data.Seed.Sql.";
            string resourceName;

            // Paramater
            try {
                resourceName = string.Concat(resourceNameBaseData, "Parameters.csv");
                using (Stream stream = assembly.GetManifestResourceStream(resourceName)) {
                    using (StreamReader reader = new StreamReader(stream, Encoding.UTF8)) {
                        CsvReader csvReader = new CsvReader(reader);
                        csvReader.Configuration.WillThrowOnMissingField = false;
                        var data = csvReader.GetRecords<Parameter>().ToArray();
                        context.Parameters.AddOrUpdate(data);
                    }
                }
                context.SaveChanges();
            } catch (System.Exception) {
                // Do nothing here as Parms are dependen from environment
                // so far only available on dev and not to be distributed.
                // throw;
            }

            // Time Zone
            resourceName = string.Concat(resourceNameBaseData, "TimeZones.csv");
            using (Stream stream = assembly.GetManifestResourceStream(resourceName)) {
                using (StreamReader reader = new StreamReader(stream, Encoding.UTF8)) {
                    CsvReader csvReader = new CsvReader(reader);
                    csvReader.Configuration.WillThrowOnMissingField = false;
                    var data = csvReader.GetRecords<TimeZone>().ToArray();
                    context.TimeZones.AddOrUpdate(data);
                }
            }
            context.SaveChanges();

            // Currencies
            resourceName = string.Concat(resourceNameBaseData, "Currencies.csv");
            using (Stream stream = assembly.GetManifestResourceStream(resourceName)) {
                using (StreamReader reader = new StreamReader(stream, Encoding.UTF8)) {
                    CsvReader csvReader = new CsvReader(reader);
                    csvReader.Configuration.WillThrowOnMissingField = false;
                    var data = csvReader.GetRecords<Currency>().ToArray();
                    context.Currencies.AddOrUpdate(data);
                }
            }
            context.SaveChanges();

            // Categories
            resourceName = string.Concat(resourceNameBaseData, "Frequencies.csv");
            using (Stream stream = assembly.GetManifestResourceStream(resourceName)) {
                using (StreamReader reader = new StreamReader(stream, Encoding.UTF8)) {
                    CsvReader csvReader = new CsvReader(reader);
                    csvReader.Configuration.WillThrowOnMissingField = false;
                    var categories = csvReader.GetRecords<Frequency>().ToArray();
                    context.Categories.AddOrUpdate(categories);
                }
            }
            context.SaveChanges();
            resourceName = string.Concat(resourceNameBaseData, "OptionTypes.csv");
            using (Stream stream = assembly.GetManifestResourceStream(resourceName)) {
                using (StreamReader reader = new StreamReader(stream, Encoding.UTF8)) {
                    CsvReader csvReader = new CsvReader(reader);
                    csvReader.Configuration.WillThrowOnMissingField = false;
                    var categories = csvReader.GetRecords<OptionType>().ToArray();
                    context.Categories.AddOrUpdate(categories);
                }
            }
            context.SaveChanges();
            resourceName = string.Concat(resourceNameBaseData, "LoadTypes.csv");
            using (Stream stream = assembly.GetManifestResourceStream(resourceName)) {
                using (StreamReader reader = new StreamReader(stream, Encoding.UTF8)) {
                    CsvReader csvReader = new CsvReader(reader);
                    csvReader.Configuration.WillThrowOnMissingField = false;
                    var categories = csvReader.GetRecords<LoadType>().ToArray();
                    context.Categories.AddOrUpdate(categories);
                }
            }
            context.SaveChanges();

            // Countries
            resourceName = string.Concat(resourceNameBaseData, "Countries.csv");
            using (Stream stream = assembly.GetManifestResourceStream(resourceName)) {
                using (StreamReader reader = new StreamReader(stream, Encoding.UTF8)) {
                    CsvReader csvReader = new CsvReader(reader);
                    csvReader.Configuration.WillThrowOnMissingField = false;
                    while (csvReader.Read()) {
                        var rec = csvReader.GetRecord<Country>();
                        var code = csvReader.GetField<string>("CurrencyCode");
                        rec.Currency = context.Currencies.Local.SingleOrDefault(c => c.Code == code);
                        context.Countries.AddOrUpdate(rec);
                    }
                }
            }
            context.SaveChanges();

            // Exchanges
            resourceName = string.Concat(resourceNameBaseData, "Exchanges.csv");
            using (Stream stream = assembly.GetManifestResourceStream(resourceName)) {
                using (StreamReader reader = new StreamReader(stream, Encoding.UTF8)) {
                    CsvReader csvReader = new CsvReader(reader);
                    csvReader.Configuration.WillThrowOnMissingField = false;
                    while (csvReader.Read()) {
                        var rec = csvReader.GetRecord<Exchange>();
                        // Currency Ref
                        var curCode = csvReader.GetField<string>("CurrencyCode");
                        rec.Currency = context.Currencies.Local.SingleOrDefault(c => c.Code == curCode);
                        // Country Ref
                        var cntCode = csvReader.GetField<string>("CountryCode");
                        rec.Country = context.Countries.Local.SingleOrDefault(c => c.Code == cntCode);
                        // Timezone Ref
                        var tizCode = csvReader.GetField<string>("TimeZoneCode");
                        rec.TimeZone = context.TimeZones.Local.SingleOrDefault(c => c.Code == tizCode);
                        context.Exchanges.AddOrUpdate(rec);
                    }
                }
            }
            context.SaveChanges();

            // LoadControl
            resourceName = string.Concat(resourceNameBaseData, "LoadControls.csv");
            using (Stream stream = assembly.GetManifestResourceStream(resourceName)) {
                using (StreamReader reader = new StreamReader(stream, Encoding.UTF8)) {
                    CsvReader csvReader = new CsvReader(reader);
                    csvReader.Configuration.WillThrowOnMissingField = false;
                    while (csvReader.Read()) {
                        var rec = csvReader.GetRecord<LoadControl>();
                        // Exchange Ref
                        var excCode = csvReader.GetField<string>("ExchangeCode");
                        rec.Exchange = context.Exchanges.Local.SingleOrDefault(c => c.Code == excCode);
                        // LoadType Ref
                        var lotCode = csvReader.GetField<string>("LoadTypeCode");
                        rec.LoadType = context.LoadTypoes.Local.SingleOrDefault(c => c.Code == lotCode);
                        // Frequency Ref
                        var freCode = csvReader.GetField<string>("FrequencyCode");
                        rec.Frequency = context.Frequencies.Local.SingleOrDefault(c => c.Code == freCode);
                        context.LoadControls.AddOrUpdate(rec);
                    }
                }
            }
            context.SaveChanges();
            
            // Loads
            // 2015-10-20: Not needed anymore 
            /*
            resourceName = string.Concat(resourceNameBaseData, "Loads.csv");
            using (Stream stream = assembly.GetManifestResourceStream(resourceName)) {
                using (StreamReader reader = new StreamReader(stream, Encoding.UTF8)) {
                    CsvReader csvReader = new CsvReader(reader);
                    csvReader.Configuration.WillThrowOnMissingField = false;
                    while (csvReader.Read()) {
                        var rec = csvReader.GetRecord<Load>();
                        // LoadControl Ref
                        var excCode = csvReader.GetField<string>("ExchangeCode");
                        var ltpCode = csvReader.GetField<string>("LoadTypeCode");
                        rec.LoadControl = context.LoadControls.SingleOrDefault<LoadControl>(a => a.Exchange.Code == excCode && a.LoadType.Code == ltpCode);
                        context.Loads.AddOrUpdate(rec);
                    }
                }
            }
            context.SaveChanges();
            */

            /******************************************************************
            /* Load sql scripts
            /*****************************************************************/
            // Stored Procedures

            // EodSymbolsApi
            resourceName = string.Concat(resourceNameBaseSql, "EodSymbolsApi.sql");
            using(Stream stream = assembly.GetManifestResourceStream(resourceName)) {
                using (StreamReader reader = new StreamReader(stream)) {
                    string sql = reader.ReadToEnd();
                    context.Database.ExecuteSqlCommand(sql);
                }
            }

            // EodQuotesApi
            resourceName = string.Concat(resourceNameBaseSql, "EodQuotesApi.sql");
            using (Stream stream = assembly.GetManifestResourceStream(resourceName)) {
                using (StreamReader reader = new StreamReader(stream)) {
                    string sql = reader.ReadToEnd();
                    context.Database.ExecuteSqlCommand(sql);
                }
            }
        }
    }
}